import sys

sys.path.extend(["F:/ML/BNC", "D:/BNC", "/www/wwwroot/BNC"])
from StockRelation.ReWriteTagUpCalendar import cursor, connection

cursor = cursor
connection = connection
TAG_COMBINE_FRQ_LINE = 3
FRQ_MIN_LINE = 31

STOCK_TAG_TB = '`stocktagrelation_spilttag={}`'.format(TAG_COMBINE_FRQ_LINE)


def initial():
    cursor.execute('TRUNCATE {};'.format(STOCK_TAG_TB))
    connection.commit()
    cursor.execute("insert into {}(SELECT * from stocktagrelation_origin);".format(STOCK_TAG_TB))
    connection.commit()
    cursor.execute(
        "DELETE FROM {} WHERE tag IN ('标普道琼斯A股','转融券标的', '融资融券', '富时罗素概念股', '新股与次新股', '沪股通', '深股通', 'MSCI预期', 'MSCI概念', '央企国资改革', '地方国资改革', '股权转让', '一带一路', '独角兽概念', '证金持股', '养老金持股', '创业板重组松绑', 'ST板块', '人民币贬值受益', '壳资源', '参股保险', '参股券商', '参股民营银行') OR tag LIKE '%国资改革%'"
        .format(STOCK_TAG_TB) + "")
    connection.commit()


if __name__ == '__main__':
    initial()
    tag_code_dict = dict()
    pre_tag = ''
    cur_tag = ''
    times = 0
    changed = True
    # 找出大于MAX_FRQ_LINE的集合
    new_keys_set_4_db = set()

    tag_sql = 'select tag,code fre from {} order by tag,code'.format(STOCK_TAG_TB)
    cursor.execute(tag_sql)

    for idx, _code in enumerate(cursor.fetchall()):
        _tag = _code[0]
        _code = _code[1]

        tag_tuple = tuple([_tag])
        if tag_tuple not in tag_code_dict.keys():
            tag_code_dict[tag_tuple] = {_code}
        else:
            tag_code_dict[tag_tuple].add(_code)

    while changed:
        changed = False
        times += 1
        big_sets = dict()
        for one_set_key in tag_code_dict.keys():
            if len(tag_code_dict[one_set_key]) > TAG_COMBINE_FRQ_LINE:
                big_sets[one_set_key] = tag_code_dict[one_set_key]

        print("sets nums :{} , big set nums:{}".format(len(tag_code_dict), len(big_sets)))
        #     对非自己的集合求交集
        new_sets = dict()
        sets_should_substract = dict()

        for one_big_set_key in big_sets.keys():

            for one_set_key in tag_code_dict.keys():
                one_big_set = big_sets[one_big_set_key]
                one_set = tag_code_dict[one_set_key]

                # 两个标签的任何一个标签小于 FRQ_MIN_LINE都是不行的
                # combine : A > LINE and B > LINE OR ONE OF (A, B) > LINE
                if one_set_key == one_big_set_key :
                    # or (
                    #         len(one_big_set) <= FRQ_MIN_LINE and len(one_set) <= FRQ_MIN_LINE)
                    continue
                # 如果有交集，生成新的集合

                # if len(one_big_set) > MAX_FRQ_LINE or len(one_set) > MAX_FRQ_LINE:
                new_set = one_big_set & one_set

                # if len(new_set) > MAX_FRQ_LINE:
                # print("spilt set from {} {} , spilt set is :{}".format(one_big_set, one_set, new_set))

                if len(new_set) > TAG_COMBINE_FRQ_LINE:

                    # if "PPP概念" in new_set and "轨道交通" in new_set and "铁路基建" in new_set:
                    #     print(new_set)
                    changed = True
                    if one_big_set_key not in sets_should_substract.keys():
                        sets_should_substract[one_big_set_key] = set()
                    if one_set_key not in sets_should_substract.keys():
                        sets_should_substract[one_set_key] = set()
                    # 去除公共集合
                    sets_should_substract[one_big_set_key].update(new_set)
                    sets_should_substract[one_set_key].update(new_set)

                    new_key_list = list()
                    for big_key in one_big_set_key:
                        if big_key not in new_key_list: new_key_list.append(big_key)
                    for one_key in one_set_key:
                        if one_key not in new_key_list: new_key_list.append(one_key)

                    new_key = tuple(sorted(new_key_list))
                    if new_key not in new_sets.keys():
                        new_sets[new_key] = set()

                    new_sets[new_key].update(new_set)

                    if "长三角一体化" in new_key:
                        print("tag-{} num-{} and tag-{} num-{} combine to {} num-{} > LINE".format(one_big_set_key, len(
                            one_big_set - new_set), one_set_key, len(one_set - new_set), new_key, len(new_set)))

        # 减去应该被去除的键
        for one_substract_key in sets_should_substract.keys():
            # tag_code_dict[one_substract_key] -= sets_should_substract[one_substract_key]
            sss = sets_should_substract[one_substract_key]
            tag_code_dict[one_substract_key] -= sss

        need_remove_tag_code_key = list()
        for tag_code_key in tag_code_dict.keys():
            if len(tag_code_dict[tag_code_key]) == 0:
                need_remove_tag_code_key.append(tag_code_key)

        for nrtck in need_remove_tag_code_key:
            tag_code_dict.pop(nrtck)
        # 添加新概念
        for new_set_key in new_sets.keys():
            tag_code_dict[new_set_key] = new_sets[new_set_key]


    # print("new keys:{}".format(new_keys_set_4_db))
    # print("times:{}".format(times))
    def rename_tuple(tup):
        st = str(tup).replace("(", "").replace(")", "").replace(",", "-").replace(" ", '').replace("'", '')
        if st[-1:] == '-':
            st = st[:-1]
        return st


    select_contain_two_sql_format = 'SELECT `CODE` FROM stocktag WHERE {}'


    def like(col, key_words):
        base_li = list()
        base_li.extend(key_words)
        st = ""
        for word in base_li:
            st += "({}  like '%,{},%' or {} like '{},%' or {} like ',%{}') and ".format(col, word, col, word, col, word)
        pass
        st = st[:-4]
        return st


    code_tag_need_replace = list()
    for tags in tag_code_dict.keys():
        # cursor.execute(select_contain_two_sql_format.format(
        #     like('tag', tags)
        # ))
        for _code in tag_code_dict[tags]:
            code_tag_need_replace.append({'res': _code, 'need_append_tags': tags})

    need_delete_code_tag = set()
    need_insert_new_code_tag = set()
    for code_tnr in code_tag_need_replace:
        _code = code_tnr['res']
        tags = code_tnr['need_append_tags']

        for tag in tags:
            if (_code, tag,) not in need_delete_code_tag:
                need_delete_code_tag.add((_code, tag,))

        if (_code, rename_tuple(tags),) not in need_insert_new_code_tag:
            need_insert_new_code_tag.add((_code, rename_tuple(tags),))

    delete_code_tag_by_CT = "delete  from {} where code = %s and tag = %s".format(STOCK_TAG_TB)
    cursor.executemany(delete_code_tag_by_CT, need_delete_code_tag)
    connection.commit()

    insert_new_code_tag = "insert into {}(code,tag) values(%s,%s)".format(STOCK_TAG_TB)
    cursor.executemany(insert_new_code_tag, need_insert_new_code_tag)
    connection.commit()

    update_code_name = 'update {} A,stocktagrelation_origin  B set A.name = B.name where A.code = B.code'.format(
        STOCK_TAG_TB)
    cursor.execute(update_code_name)
    connection.commit()

    tag_fre = 'select tag, count(*) as fre from {} group by tag order by fre DESC'.format(STOCK_TAG_TB)
    cursor.execute(tag_fre)
    rr = cursor.fetchall()
    fres_data = [r[1] for r in rr]

    origin_tag_fre = 'select tag, count(*) as fre from stocktagrelation_origin group by tag order by fre DESC'
    cursor.execute(origin_tag_fre)
    rr = cursor.fetchall()
    origin_fres_data = [r[1] for r in rr]


    def draw_bar(fres_data, origin_data):
        import matplotlib.pyplot as plt
        import seaborn as sns
        import numpy as np
        # 提取多个数据
        # 华为和无线耳机的数据
        fig = plt.figure()
        ax1 = fig.add_subplot(211 + 0)
        ax1.set_title("切割大小为:{} ,标签最小限制为:{} 的标签频数分布图".format(TAG_COMBINE_FRQ_LINE, FRQ_MIN_LINE))
        sns.distplot(fres_data, bins=40, rug=True, ax=ax1, kde=True)
        ax2 = fig.add_subplot(211 + 1)
        ax2.set_title("原始标签频数分布图")
        sns.distplot(origin_data, bins=40, rug=True, ax=ax2, kde=True)
        plt.show()
    draw_bar(fres_data, origin_data=origin_fres_data)
